﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
	public class FileRecordProvider : PhotoGallery.Core.Providers.DALProvider.IFileRecordProvider
	{
		#region Sql
        private static readonly string SqlSelectFileRecord = "SELECT [Id], [Path],[Name],[Extention] FROM [FileRecord] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertFileRecord = "INSERT INTO [FileRecord]([Path],[Name],[Extention]) values(@Path,@Name,@Extention);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdateFileRecord = "UPDATE [FileRecord] SET [Path] = @Path,[Name] = @Name,[Extention] = @Extention WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteFileRecord = "DELETE [FileRecord] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllFileRecord = "SELECT [Id], [Path],[Name],[Extention] FROM [FileRecord];";

        private static readonly string SqlFindFileRecord = "SELECT [Id], [Path],[Name],[Extention] FROM [FileRecord] WHERE [Path]=@Path;";


		#endregion

#region Procedures
/*

-- Begin of FileRecord
IF OBJECT_ID(N'[USP_FileRecord_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_FileRecord_Delete_By_Id]

IF OBJECT_ID(N'[USP_FileRecord_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_FileRecord_Select_By_Id]

IF OBJECT_ID(N'[USP_FileRecord_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_FileRecord_Insert]

IF OBJECT_ID(N'[USP_FileRecord_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_FileRecord_Update_By_Id]

IF OBJECT_ID(N'[USP_FileRecord_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_FileRecord_SelectAll]

GO

--region [USP_FileRecord_Delete_By_Id]
CREATE PROCEDURE [USP_FileRecord_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [FileRecord] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_FileRecord_Select_By_Id]
CREATE PROCEDURE [USP_FileRecord_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Path],[Name],[Extention] FROM [FileRecord] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_FileRecord_Insert]
CREATE PROCEDURE [USP_FileRecord_Insert]
	@Id	int,
	@Path	nvarchar(200),
	@Name	nvarchar(50),
	@Extention	nvarchar(20)
AS
SET NOCOUNT ON
INSERT INTO [FileRecord]([Id],[Path],[Name],[Extention]) values(@Id,@Path,@Name,@Extention);
--endregion
GO

--region [USP_FileRecord_Update_By_Id]
CREATE PROCEDURE [USP_FileRecord_Update_By_Id]
	@Path	nvarchar(200),
	@Name	nvarchar(50),
	@Extention	nvarchar(20),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [FileRecord] SET [Path] = @Path,[Name] = @Name,[Extention] = @Extention WHERE  [Id]=@Id;
--endregion
GO

--region [USP_FileRecord_SelectAll]
CREATE PROCEDURE [USP_FileRecord_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Path],[Name],[Extention] FROM [FileRecord];
--endregion
GO


-- End of FileRecord
*/
#endregion

		#region IFileRecordProvider Members
        public Core.Business.FileRecord Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectFileRecord);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.FileRecord fileRecord = new Core.Business.FileRecord();

                if (!reader.IsDBNull(0)) fileRecord.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) fileRecord.Path = reader.GetString(1);
                if (!reader.IsDBNull(2)) fileRecord.Name = reader.GetString(2);
                if (!reader.IsDBNull(3)) fileRecord.Extention = reader.GetString(3);

                reader.Close();
                return fileRecord;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.FileRecord fileRecord)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Path", SqlDbType.NVarChar, fileRecord.Path);
            sql.AddParameter("@Name", SqlDbType.NVarChar, fileRecord.Name);
            sql.AddParameter("@Extention", SqlDbType.NVarChar, fileRecord.Extention);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertFileRecord);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) fileRecord.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.FileRecord fileRecord)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Path", SqlDbType.NVarChar, fileRecord.Path);
            sql.AddParameter("@Name", SqlDbType.NVarChar, fileRecord.Name);
            sql.AddParameter("@Extention", SqlDbType.NVarChar, fileRecord.Extention);
            sql.AddParameter("@Id", SqlDbType.Int, fileRecord.Id);
            sql.ExecuteSql(SqlUpdateFileRecord);
        }

        public void Delete(Core.Business.FileRecord fileRecord)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, fileRecord.Id);
            sql.ExecuteSql(SqlDeleteFileRecord);
        }

        public IList<Core.Business.FileRecord> GetAllFileRecord()
        {
            IList<Core.Business.FileRecord> fileRecordlist = new List<Core.Business.FileRecord>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllFileRecord);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.FileRecord fileRecord = new Core.Business.FileRecord();

                    if (!reader.IsDBNull(0)) fileRecord.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) fileRecord.Path = reader.GetString(1);
                    if (!reader.IsDBNull(2)) fileRecord.Name = reader.GetString(2);
                    if (!reader.IsDBNull(3)) fileRecord.Extention = reader.GetString(3);

                    fileRecord.MarkOld();
                    fileRecordlist.Add(fileRecord);
                }
                reader.Close();
            }
            return fileRecordlist;
        }
		#endregion


        public FileRecord Find(string path)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Path", SqlDbType.NVarChar, path);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlFindFileRecord);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.FileRecord fileRecord = new Core.Business.FileRecord();

                if (!reader.IsDBNull(0)) fileRecord.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) fileRecord.Path = reader.GetString(1);
                if (!reader.IsDBNull(2)) fileRecord.Name = reader.GetString(2);
                if (!reader.IsDBNull(3)) fileRecord.Extention = reader.GetString(3);

                reader.Close();
                return fileRecord;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }    
        }
    }
}
